﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Unis.Framework.Core.Utility;
using Unis.Framework.ORM.Persistence;
using Unis.ORM.Persistence;

namespace Unis.Framework.RuleBase.Common
{
    public class DatabaseExcuteBySql
    {
        // private static readonly Database Db = DatabaseHelper.CreateDatabase();

        /// <summary>
        /// 用来运行SQL的Select语句取得List<T>对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static List<T> QueryBySql<T>(string sql) where T : class,new()
        {
            try
            {
                var dt = QuerybySql(sql);
                return dt.DataTableToList<T>();
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// 用来运行SQL的Select语句取得Datatable
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回Datatable</returns>
        public static DataTable QuerybySql(string sql)
        {
            try
            {
                return QuerybySql(null, sql, "tbQueryTable");
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }


        /// <summary>
        /// 用来运行SQL的Select语句取得Datatable，并指定Tablename
        /// </summary>
        /// <param name="dbName"></param>
        /// <param name="sql"></param>
        /// <param name="tablename"></param>
        /// <returns>返回Datatable</returns>
        public static DataTable QuerybySql(string dbName, string sql, string tablename)
        {
            try
            {
                var db = String.IsNullOrEmpty(dbName) ?
                    DatabaseHelper.CreateDatabase() :
                    DatabaseHelper.CreateDatabase(dbName.ToString(CultureInfo.InvariantCulture));
                var dt = db.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dt.TableName = tablename;
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        /// <summary>
        /// 用来执行Insert,Update,Delete的Sql语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回影响的笔数</returns>
        public static int ExecuteNonQuery(string dbName, string sql)
        {
            var db = String.IsNullOrEmpty(dbName) ? DatabaseHelper.CreateDatabase() : DatabaseHelper.CreateDatabase(dbName.ToString(CultureInfo.InvariantCulture));
            var i = db.ExecuteNonQuery(CommandType.Text, sql);
            return i;
        }

        /// <summary>
        /// 用来运行SQL的存储过程取得DatataSet
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="parameterValues">存储过程参数</param>
        /// <returns></returns>
        public static DataSet QuerybyProc(string procName, params object[] parameterValues)
        {
            try
            {
                return QuerybyProc(null, procName, parameterValues);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 用来运行SQL的存储过程取得DatataSet
        /// </summary>
        /// <param name="db">数据源</param>
        /// <param name="procName">存储过程名</param>
        /// <param name="parameterValues">存储过程参数</param>
        /// <returns></returns>
        public static DataSet QuerybyProc(Database db, string procName, params object[] parameterValues)
        {
            try
            {
                return parameterValues != null ? db.ExecuteDataSet(procName, parameterValues) : db.ExecuteDataSet(procName);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 用来执行Sql语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回第一行第一列的值</returns>
        public static T ExecuteScalar<T>(string sql)
        {
            return ExecuteScalar<T>("", sql);
        }

        /// <summary>
        /// 用来执行Sql语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回第一行第一列的值</returns>
        public static T ExecuteScalar<T>(string dbName, string sql)
        {
            var db = String.IsNullOrEmpty(dbName) ? DatabaseHelper.CreateDatabase() : DatabaseHelper.CreateDatabase(dbName.ToString(CultureInfo.InvariantCulture));
            var i = db.ExecuteScalar(CommandType.Text, sql);
            return (T)Convert.ChangeType(i, typeof(T));
        }
    }
}
